/*
 * 
 * Copyright (C) 2008 VirtualStaticVoid <virtualstaticvoid@gmail.com>
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http: *www.gnu.org/licenses/>.
 *
*/


use AomDB
go

if exists ( select * from sys.objects where object_id = object_id('aom.sp_generateRelationTypeTrigger') and type = 'P' )
  drop procedure aom.sp_generateRelationTypeTrigger
go

create procedure aom.sp_generateRelationTypeTrigger
(
  @i_relationtypeid int
)
-- with execute as owner
as

  declare @sql nvarchar(MAX)

  declare @relationtypename nvarchar(50)
  declare @attributetypeid int
  declare @attributetypename nvarchar(50)
  declare @attributedatatype int
  declare @viewname nvarchar(60)
  declare @triggername nvarchar(60)
  declare @variabledeclares nvarchar(MAX)
  declare @fieldlist nvarchar(MAX)
  declare @variablelist nvarchar(MAX)
  declare @attributeset nvarchar(MAX)
  declare @deleteattributes nvarchar(MAX)
  declare @varname nvarchar(255)

  select @relationtypename = Name
  from aom.RelationType
  where Id = @i_relationtypeid

  select @viewname = 'Relation' + @relationtypename,
         @triggername = 'trg_Relation' + @relationtypename,
         @variabledeclares = '',
         @fieldlist = '',
         @variablelist = '',
         @attributeset = '',
         @deleteattributes = ''

  declare cursorAttributeTypes cursor fast_forward
  for select Id, Name, DataType
      from aom.RelationAttributeType
      where RelationTypeId = @i_relationtypeid
      order by Name

  open cursorAttributeTypes

  fetch next from cursorAttributeTypes
  into @attributetypeid, @attributetypename, @attributedatatype

  while @@fetch_status = 0
  begin

    select @varname = '@data_' + convert(nvarchar, @attributetypeid)

    select @variablelist = @variablelist + ', ' + @varname

    select @fieldlist = @fieldlist + ', [' + @attributetypename + ']'

    select @variabledeclares = @variabledeclares + '  declare ' + @varname + N' ' +

      ( case @attributedatatype

          when 0 then 'bit'
          when 1 then 'int'
          when 2 then 'decimal(38, 15)'
          when 3 then 'datetime'
          when 4 then 'nvarchar(255)'
          when 5 then 'int'
          when 6 then 'xml'
          when 7 then 'int'
          else '!UNKNOWN!'

        end ) + '
'

    select @attributeset = @attributeset + N'      exec aom.fn_setRelationAttribute' +  

      ( case @attributedatatype

          when 0 then 'Bool'
          when 1 then 'Int'
          when 2 then 'Decimal'
          when 3 then 'DateTime'
          when 4 then 'String'
          when 5 then 'Entity'
          when 6 then 'Xml'
          when 7 then 'Enum'
          else '!UNKNOWN!'

        end )

  + ' ' + convert(nvarchar, @attributetypeid) + ', @relationid, ' + @varname + '  
'

    select @deleteattributes = @deleteattributes + N'      exec aom.fn_setRelationAttribute' +  

      ( case @attributedatatype

          when 0 then 'Bool'
          when 1 then 'Int'
          when 2 then 'Decimal'
          when 3 then 'DateTime'
          when 4 then 'String'
          when 5 then 'Entity'
          when 6 then 'Xml'
          when 7 then 'Enum'
          else '!UNKNOWN!'

        end ) 

  + ' ' + convert(nvarchar, @attributetypeid) + ', @relationid, null
'

    fetch next from cursorAttributeTypes
    into @attributetypeid, @attributetypename, @attributedatatype

  end

  close cursorAttributeTypes
  deallocate cursorAttributeTypes

/* insert */

  select @sql = N'
/*
 * GENERATED TRIGGER - DO NOT EDIT
 */
create trigger aomgen.[' + @triggername + 'Insert] on aomgen.[' + @viewname + ']
instead of insert
as
begin

  --print ''Inserting...''

  set nocount on

  declare @relationid int
  declare @leftentityid int
  declare @rightentityid int
  ' + @variabledeclares + '

  declare cursorInserted cursor fast_forward
  for select LeftEntityId, RightEntityId' + @fieldlist + '
      from inserted

  open cursorInserted

  fetch next from cursorInserted 
  into @leftentityid, @rightentityid' + @variablelist + '

  while @@fetch_status = 0
  begin

    insert into aom.Relation ( RelationTypeId, LeftEntityId, RightEntityId )
    values ( ' + convert(nvarchar, @i_relationtypeid) + ', @leftentityid, @rightentityid ) 

    select @relationid = scope_identity()

  ' + @attributeset + 
  '    
    fetch next from cursorInserted 
    into @leftentityid, @rightentityid' + @variablelist + '

  end

  close cursorInserted
  deallocate cursorInserted

  set nocount off  

end
'

  -- print @sql
  exec sp_executesql @sql

/* update */

  select @sql = N'
/*
 * GENERATED TRIGGER - DO NOT EDIT
 */
create trigger aomgen.[' + @triggername + 'Update] on aomgen.[' + @viewname + ']
instead of update
as
begin

  --print ''Updating...''

  set nocount on

  declare @relationid int
  declare @relationversion rowversion
  declare @leftentityid int
  declare @rightentityid int
  ' + @variabledeclares + '

  declare cursorUpdated cursor fast_forward
  for select Id, Version, LeftEntityId, RightEntityId' + @fieldlist + '
      from inserted

  open cursorUpdated

  fetch next from cursorUpdated 
  into @relationid, @relationversion, @leftentityid, @rightentityid' + @variablelist + '

  while @@fetch_status = 0
  begin

    update aom.Relation 
    set LeftEntityId = @leftentityid,
        RightEntityId = @rightentityid
    where Id = @relationid
      and Version = @relationversion

    if ( @@rowcount = 0 )
    begin
      raiserror(''Concurrency error. Failed to update relation. [Id = %d]'', 16, 1, @relationid)
      break
    end

  ' + @attributeset + 
  '    
    fetch next from cursorUpdated 
    into @relationid, @relationversion, @leftentityid, @rightentityid' + @variablelist + '

  end

  close cursorUpdated
  deallocate cursorUpdated

  set nocount off  

end
'

  -- print @sql
  exec sp_executesql @sql

/* delete */


  select @sql = N'
/*
 * GENERATED TRIGGER - DO NOT EDIT
 */
create trigger aomgen.[' + @triggername + 'Delete] on aomgen.[' + @viewname + ']
instead of delete
as
begin

  --print ''Deleting...''

  set nocount on

  declare @relationid int
  declare @relationversion rowversion

  declare cursorDeleted cursor fast_forward
  for select Id, Version
      from deleted

  open cursorDeleted

  fetch next from cursorDeleted 
  into @relationid, @relationversion

  while @@fetch_status = 0
  begin

    if not exists ( select * from aom.Relation where Id = @relationid and Version = @relationversion )
    begin
      raiserror(''Concurrency error. Failed to delete relation. [Id = %d]'', 16, 1, @relationid)
      break
    end

  ' + @deleteattributes + '

    delete aom.Relation 
    where Id = @relationid
      and Version = @relationversion

    fetch next from cursorDeleted 
    into @relationid, @relationversion

  end

  close cursorDeleted
  deallocate cursorDeleted

  set nocount off  

end
'

  -- print @sql
  exec sp_executesql @sql

  return @@error

go
